package com.icss.helper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.validator.GenericValidator;
import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.icss.helper.BatchSql;
import com.icss.helper.ProcHelper;
import com.icss.helper.StringHelper;
import com.icss.helper.DBHelper;
import com.icss.helper.SpringHelper;

/**
 * @author shixiaolong
 * @dateApr 16, 2012
 */
public class DBHelper extends JdbcDaoSupport {
	public static Logger logger = Logger.getLogger(DBHelper.class);
	public final Logger log = Logger.getLogger(this.getClass());
	public StringHelper strh;
	public LobHandler lobHandler;
	public TransactionTemplate transactionTemplate;

	public synchronized static DBHelper getInstance() {
		DBHelper db = null;
		try {
			db = (DBHelper) SpringHelper.getBean("dbHelper");
		} catch (Exception e) {
			logger.error("������ݲ������ʧ�ܣ�");
		}
		return db;
	}

	public synchronized static Connection getDBConnection() {
		Connection conn = null;
		DBHelper db = DBHelper.getInstance();
		try {
			conn = db.getConnection();
		} catch (Exception e) {
			logger.error("��ȡ��ݿ�Connectionʧ�ܣ�");
			logger.error(e.toString());
		}
		return conn;
	}

	/**
	 * ��ѯ���ȷ��ʱ
	 * 
	 * @param sql
	 *            �����sql���: select *from table where name=? and msisdn like
	 *            '%?%'
	 * @param objects
	 *            :����Ĳ��� new Object[] { "����",1234}
	 * @param rowMapper
	 * @return
	 */
	public Object queryForObject(String sql, Object[] objects,
			RowMapper rowMapper) {
		Object object = null;
		object = this.getJdbcTemplate().queryForObject(sql, objects, rowMapper);
		return object;
	}

	/**
	 * ����һ���¼-�����浽�����¼
	 * 
	 * @param sql
	 *            �����sql���: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public Map queryForMap(String sql, Object[] objects) {
		Map map = null;
		try {
			map = this.getJdbcTemplate().queryForMap(sql, objects);
		} catch (EmptyResultDataAccessException e) {

		} catch (Exception e) {
			logger.error(e);
			logger.error(strh.getSql(sql, objects));
		}
		if (map == null)
			map = new HashMap();
		return map;
	}

	/**
	 * ����һ���¼-���浽�����¼ �в���
	 * 
	 * @param sql
	 *            �����sql���: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public Map queryForMap(String sql, Object[] objects,
			HttpServletRequest request, String node_id) {
		Map map = null;
		try {
			map = this.getJdbcTemplate().queryForMap(sql, objects);
		} catch (EmptyResultDataAccessException e) {

		} catch (Exception e) {
			logger.error(e);
			logger.error(strh.getSql(sql, objects));
		}
		if (map == null)
			map = new HashMap();
		return map;
	}

	public Map queryForMap(String sql) {
		Map map = null;
		try {
			map = this.getJdbcTemplate().queryForMap(sql, null);
		} catch (EmptyResultDataAccessException e) {

		} catch (Exception e) {
			logger.error(e);
			logger.error(sql);
		}
		if (map == null)
			map = new HashMap();
		return map;
	}

	public Map queryForMap(String sql, HttpServletRequest request,
			String node_id) {
		Map map = null;
		try {
			map = this.getJdbcTemplate().queryForMap(sql, null);
		} catch (EmptyResultDataAccessException e) {

		} catch (Exception e) {
			logger.error(e);
			logger.error(sql);
		}
		if (map == null)
			map = new HashMap();
		return map;
	}

	public String queryForString(String sql, Object[] args) {
		StringHelper strh = new StringHelper();
		try {
			return strh.notEmpty(this.getJdbcTemplate().queryForObject(sql,
					args, String.class));
		} catch (Exception ex) {
			logger.error(ex);
			logger.error(strh.getSql(sql, args));
			return "";
		}
	}

	public String queryForString(String sql, Object[] args,
			HttpServletRequest request, String node_id) {
		StringHelper strh = new StringHelper();
		try {
			return strh.notEmpty(this.getJdbcTemplate().queryForObject(sql,
					args, String.class));
		} catch (Exception ex) {
			logger.error(ex);
			logger.error(strh.getSql(sql, args));
			return "";
		}
	}

	public String queryForString(String sql) {
		StringHelper strh = new StringHelper();
		try {
			return strh.notEmpty(this.getJdbcTemplate().queryForObject(sql,
					null, String.class));
		} catch (Exception ex) {
			System.out.println("!!!!!!!!!!!!!!!!!");
			logger.error(sql);
			return "";
		}
	}

	public String queryForString(String sql, HttpServletRequest request,
			String node_id) {
		StringHelper strh = new StringHelper();
		try {
			return strh.notEmpty(this.getJdbcTemplate().queryForObject(sql,
					null, String.class));
		} catch (Exception ex) {
			logger.error(sql);
			return "";
		}
	}

	/**
	 * ����һ���¼�޳�nullֵ
	 * 
	 * @param sql
	 *            �����sql���: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public Map queryForMapNotNull(String sql, Object[] objects) {
		Map map = null;
		Map temp = new HashMap();
		StringHelper strh = new StringHelper();
		try {
			map = this.getJdbcTemplate().queryForMap(sql, objects);
			if (map != null) {
				Set s = map.keySet();
				for (Iterator iter = s.iterator(); iter.hasNext();) {
					String key = strh.notEmpty(iter.next()).toString();
					String value = strh.notEmpty(map.get(key)).toString();
					temp.put(key, value);
				}
				map.clear();
			}
		} catch (Exception e) {
			logger.error(e);
		}
		return temp;
	}

	/**
	 * ������Ӧsequence����һ��ֵ
	 * 
	 * @param sequenceName
	 *            sequence���
	 * @return �¸�ֵ
	 */
	public String getNextSequenceValue(String sequenceName) {
		Map map = null;
		String nextVal = "";
		try {
			map = this.getJdbcTemplate().queryForMap(
					"select " + sequenceName + ".NEXTVAL SEQ from dual");
			nextVal = map.get("SEQ").toString();
		} catch (Exception e) {
			logger.error(e);
		}
		return nextVal;
	}

	public String getSysdate(String time_formate) {
		Map map = null;
		String sysdate = "";
		try {
			map = this.getJdbcTemplate().queryForMap(
					"select to_char(sysdate,'" + time_formate
							+ "') date_time from dual");
			sysdate = map.get("DATE_TIME").toString();
		} catch (Exception e) {
			logger.error(e);
		}
		return sysdate;
	}

	/**
	 * ����һ���¼
	 * 
	 * @param sql
	 *            �����sql���: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public List queryForList(String sql, Object[] objects) {
		List list = null;
		try {
			list = this.getJdbcTemplate().queryForList(sql, objects);
		} catch (Exception e) {
			logger.error(e);
		}
		if (list == null)
			list = new ArrayList();
		return list;
	}

	public List queryForList(String sql, Object[] objects,
			HttpServletRequest request, String node_id) {
		List list = null;
		try {
			list = this.getJdbcTemplate().queryForList(sql, objects);
		} catch (Exception e) {
			logger.error(e);
		}
		if (list == null)
			list = new ArrayList();
		return list;
	}

	/**
	 * ��ѯ�����ȷ��ʱ
	 * 
	 * @param sql
	 *            sql_whereƴ�� sql="select * from table where name='"+v_name+"'";
	 * @param objects
	 * @return
	 */
	public List queryForList(String sql) {
		List list = null;
		try {
			list = this.getJdbcTemplate().queryForList(sql);
		} catch (Exception e) {
			logger.error(e);
			logger.error(sql);
		}
		return list;
	}

	public List queryForList(String sql, HttpServletRequest request,
			String node_id) {
		List list = null;
		try {
			list = this.getJdbcTemplate().queryForList(sql);
		} catch (Exception e) {
			logger.error(e);
			logger.error(sql);
		}
		return list;
	}

	/**
	 * insert,update,delete ����
	 * 
	 * @param sql
	 *            �������� sql="insert into tables values(?,?)";
	 * @param objects
	 * @return 0:ʧ�� 1:�ɹ�
	 */
	public int update(String sql, Object[] objects) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql, objects);
		} catch (Exception e) {
			exc = 0;
			logger.error(e);
			logger.error(strh.getSql(sql, objects));
		}
		return exc;
	}

	public int update(String sql, Object[] objects, HttpServletRequest request,
			String node_id) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql, objects);
		} catch (Exception e) {
			exc = 0;
			logger.error(e);
			logger.error(strh.getSql(sql, objects));
		}
		return exc;
	}

	public int update(String sql) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql);
		} catch (Exception e) {
			exc = 0;
			logger.error(sql);
			logger.error(e);
		}
		return exc;
	}

	public int update(String sql, HttpServletRequest request, String node_id) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql);
		} catch (Exception e) {
			exc = 0;
			logger.error(sql);
			logger.error(e);
		}
		return exc;
	}

	/**
	 * ������¼��
	 * 
	 * @param sql
	 *            �����sql��� select count(*) from table where name=?
	 * @param objects
	 *            ����ֵ
	 * @return -1:��ݿ��쳣
	 */
	public int queryForInt(String sql, Object[] objects) {
		int exc = -1;
		try {
			exc = this.getJdbcTemplate().queryForInt(sql, objects);
		} catch (Exception e) {
			exc = -1;
			logger.error(e);
		}
		return exc;
	}

	public int queryForInt(String sql, Object[] objects,
			HttpServletRequest request, String node_id) {
		int exc = -1;
		try {
			exc = this.getJdbcTemplate().queryForInt(sql, objects);
		} catch (Exception e) {
			exc = -1;
			logger.error(e);
		}
		return exc;
	}

	/**
	 * ������¼��
	 * 
	 * @param sql
	 *            �����sql���ֱ��ƴ�Ӻ� sql="select count(*) from table where
	 *            name='"+mike+"'"
	 * @return
	 */
	public int queryForInt(String sql) {

		return this.getJdbcTemplate().queryForInt(sql);
	}

	public int queryForInt(String sql, HttpServletRequest request,
			String node_id) {
		return this.getJdbcTemplate().queryForInt(sql);
	}

	/**
	 * ������¼��--���ؼ�¼���int��Χ
	 * 
	 * @param sql
	 *            �����sql���ֱ��ƴ�Ӻ� sql="select count(*) from table where
	 *            name='"+mike+"'"
	 * @return
	 */
	public Long queryForLong(String sql) {
		return this.getJdbcTemplate().queryForLong(sql);
	}

	// /**
	// * displayTag ��ݿ��ҳ
	// *
	// * @param sql
	// * @param pageSize
	// * @param request
	// * @return
	// */
	// @SuppressWarnings("unchecked")
	// public List getForList(String sql, int pageSize, HttpServletRequest
	// request) {
	// // ҳ���ʶ�Ĳ�����
	// String pageIndexName = new org.displaytag.util.ParamEncoder("row")
	// .encodeParameterName(org.displaytag.tags.TableTagParameters.PARAMETER_PAGE);
	// // �Զ�ȡ�õ�ǰҳ���ʶ,��ʼĬ��Ϊ0
	// int pageIndex = GenericValidator.isBlankOrNull(request
	// .getParameter(pageIndexName)) ? 0 : (Integer.parseInt(request
	// .getParameter(pageIndexName)) - 1);
	// // beginIndex��size��displayTag��ʾʱ����
	// // beginIndexΪ��ǰ��ʼ����
	// // sizeΪ����,displayTag���size��pageSize�Զ�������ҳ��
	// request.setAttribute("beginIndex", pageSize * pageIndex);
	// int size = this.queryForInt("select count(1) from (" + sql + ")");
	// request.setAttribute("size", new Integer(size));
	// int first = pageIndex * pageSize + 1;
	// int last = first + pageSize;
	// if (request.getParameter("6578706f7274") == null) {
	// sql = "SELECT * FROM (SELECT AA.*, rownum rr FROM (" + sql
	// + " )AA )BB where rr<" + last + " and rr>=" + first;
	// }
	// System.out.println("��ҳSQL��"+sql);
	// return this.queryForList(sql);
	// }

	// public List getForList(String sql, int pageSize, HttpServletRequest
	// request,String node_id) {
	// // ҳ���ʶ�Ĳ�����
	// String pageIndexName = new org.displaytag.util.ParamEncoder("row")
	// .encodeParameterName(org.displaytag.tags.TableTagParameters.PARAMETER_PAGE);
	// // �Զ�ȡ�õ�ǰҳ���ʶ,��ʼĬ��Ϊ0
	// int pageIndex = GenericValidator.isBlankOrNull(request
	// .getParameter(pageIndexName)) ? 0 : (Integer.parseInt(request
	// .getParameter(pageIndexName)) - 1);
	// // beginIndex��size��displayTag��ʾʱ����
	// // beginIndexΪ��ǰ��ʼ����
	// // sizeΪ����,displayTag���size��pageSize�Զ�������ҳ��
	// request.setAttribute("beginIndex", pageSize * pageIndex);
	// int size = this.queryForInt("select count(1) from (" + sql + ")");
	// request.setAttribute("size", new Integer(size));
	// int first = pageIndex * pageSize + 1;
	// int last = first + pageSize;
	// if (request.getParameter("6578706f7274") == null) {
	// sql = "SELECT * FROM (SELECT AA.*, rownum rr FROM (" + sql
	// + " )AA )BB where rr<" + last + " and rr>=" + first;
	// }
	// System.out.println("��ҳSQL��"+sql);
	// this.saveOperateHis(request,sql, node_id);
	// return this.queryForList(sql);
	// }

	public TransactionTemplate getTransactionTemplate() {
		return transactionTemplate;
	}

	public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
		this.transactionTemplate = transactionTemplate;
	}

	/**
	 * ������
	 * 
	 * @param batchSqls
	 * @return
	 */
	public int doInTransaction(final BatchSql batchSql) {
		int exc = 1;
		if (batchSql == null) {
			exc = 0;
		}
		try {
			transactionTemplate.execute(new TransactionCallbackWithoutResult() {
				public void doInTransactionWithoutResult(
						TransactionStatus status) {
					List sqlList = batchSql.getSqlList();
					for (int i = 0; i < sqlList.size(); i++) {
						Map sqlMap = (Map) sqlList.get(i);
						String sql = (String) sqlMap.get("sql");
						Object[] objects = (Object[]) sqlMap.get("objects");
						String sql_type = (String) sqlMap.get("sql_type");
						// ����(���ֶβ���
						if (sql_type != null && sql_type.equals("clob")) {
							int[] colIndex = (int[]) sqlMap.get("clob_index");
							updateClob(sql, objects, colIndex);
						} else {
							getJdbcTemplate().update(sql, objects);
						}
					}
				}
			});
		} catch (Exception e) {
			exc = 0;
			logger.error(e);
		}
		return exc;
	}

	@SuppressWarnings("unchecked")
	public int doInTransaction(final BatchSql batchSql,
			HttpServletRequest request, String node_id) {
		int exc = 1;
		if (batchSql == null) {
			exc = 0;
		}
		try {
			transactionTemplate.execute(new TransactionCallbackWithoutResult() {
				public void doInTransactionWithoutResult(
						TransactionStatus status) {
					List sqlList = batchSql.getSqlList();
					for (int i = 0; i < sqlList.size(); i++) {
						Map sqlMap = (Map) sqlList.get(i);
						String sql = (String) sqlMap.get("sql");
						Object[] objects = (Object[]) sqlMap.get("objects");
						String sql_type = (String) sqlMap.get("sql_type");
						// ����(���ֶβ���
						if (sql_type != null && sql_type.equals("clob")) {
							int[] colIndex = (int[]) sqlMap.get("clob_index");
							updateClob(sql, objects, colIndex);
						} else {
							getJdbcTemplate().update(sql, objects);
						}
					}
				}
			});

			try {
				String sql = "";
				List sqlList = batchSql.getSqlList();
				for (int i = 0; i < sqlList.size(); i++) {
					Map sqlMap = (Map) sqlList.get(i);
					String sql2 = (String) sqlMap.get("sql");
					Object[] objects = (Object[]) sqlMap.get("objects");
					sql += strh.getSql(sql2, objects);

				}
				logger.debug("�����¼��" + sql);
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		} catch (Exception e) {
			exc = 0;
			logger.error(e);
		}
		return exc;
	}

	/**
	 * ע��
	 * 
	 * @param sql
	 * @param inParam
	 * @param out
	 * @return
	 */
	public ProcHelper getProcHelper(String sql) {
		ProcHelper proc = null;
		try {
			proc = new ProcHelper(this.getDataSource(), sql);
			proc.setSql(sql);
		} catch (Exception e) {
			logger.error(e.toString());
		}
		return proc;
	}

	/**
	 * 
	 * @param sql
	 *            Ҫִ�е�sql
	 * @param objects
	 *            ��������
	 * @param clobIndex
	 *            ָ�����ֶεĲ������
	 * @return
	 */
	public int updateClob(final String sql, final Object[] objects,
			final int[] clobIndex) {
		int exc = 1;
		try {
			getJdbcTemplate()
					.execute(
							sql,
							new AbstractLobCreatingPreparedStatementCallback(
									lobHandler) {
								protected void setValues(PreparedStatement ps,
										LobCreator lobCreator)
										throws SQLException {
									boolean clobFlag = false;
									for (int i = 1; i <= objects.length; i++) {
										Object param = objects[i - 1];
										clobFlag = false;
										for (int j = 0; j < clobIndex.length; j++) {

											if (i == clobIndex[j]) {
												clobFlag = true;
												break;
											}
										}
										if (clobFlag == true) {
											lobCreator.setClobAsString(ps, i,
													(String) param);
										} else {
											ps.setObject(i, param);
										}

									}
								}
							});

		} catch (Exception e) {
			exc = 0;
			logger.error(e);
		}
		exc = 1;
		return exc;

	}

	public void setStrh(StringHelper strh) {
		this.strh = strh;
	}

	public void setLobHandler(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}

	// /**
	// * �������������
	// */
	// public String addSortString(HttpServletRequest request,
	// String otherSortColumn) {
	// String sort = "";
	// // String pageIndexName = new org.displaytag.util.ParamEncoder("row")
	// // .encodeParameterName(org.displaytag.tags.TableTagParameters.);
	// String sortParameterName = new ParamEncoder("row")
	// .encodeParameterName(TableTagParameters.PARAMETER_SORT);
	// String sortColumnName = request.getParameter(sortParameterName);
	// String orderParameter = new ParamEncoder("row")
	// .encodeParameterName(TableTagParameters.PARAMETER_ORDER);
	// String orderType = request.getParameter(orderParameter);
	//
	// if (sortColumnName != null && !sortColumnName.equals("")) {
	// sort = " order by " + sortColumnName;
	// if (orderType != null && !orderType.equals("")) {
	// sort += orderType.equals("1") ? " asc " : " desc";
	// }
	// if (!otherSortColumn.equals(""))
	// sort += "," + otherSortColumn;
	// } else if (!otherSortColumn.equals("")) {
	// sort += " order by " + otherSortColumn;
	// }
	// return sort;
	//
	// }
	
	
	/**
	 * displayTag ��ݿ��ҳ
	 * 
	 * @param sql
	 * @param pageSize
	 * @param request
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getForList(String sql, int pageSize, HttpServletRequest request) {
		// ҳ���ʶ�Ĳ�����
		String pageIndexName = new org.displaytag.util.ParamEncoder("row")
				.encodeParameterName(org.displaytag.tags.TableTagParameters.PARAMETER_PAGE);
		// �Զ�ȡ�õ�ǰҳ���ʶ,��ʼĬ��Ϊ0
		int pageIndex = GenericValidator.isBlankOrNull(request
				.getParameter(pageIndexName)) ? 0 : (Integer.parseInt(request
				.getParameter(pageIndexName)) - 1);
		// beginIndex��size��displayTag��ʾʱ����
		// beginIndexΪ��ǰ��ʼ����
		// sizeΪ����,displayTag���size��pageSize�Զ�������ҳ��
		request.setAttribute("beginIndex", pageSize * pageIndex);
		int size = this.queryForInt("select count(1) from (" + sql + ")");
		request.setAttribute("size", new Integer(size));
		int first = pageIndex * pageSize + 1;
		int last = first + pageSize;
		if (request.getParameter("6578706f7274") == null) {
			sql = "SELECT * FROM (SELECT AA.*, rownum rr  FROM (" + sql
					+ " )AA  )BB where rr<" + last + " and rr>=" + first;
		}
		return this.queryForList(sql);
	}

}